﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using AddressBookDev.ViewModels;

namespace AddressBookDev.Models
{
    public static class ContactsModel
    {
        public static List<ContactViewModel> GetContacts(int userId)
        {
            List<ContactViewModel> contacts = new List<ContactViewModel>();

            using (SqlConnection connection = new SqlConnection(Utilities.Configuration.ConnectionString))
            {
                const string sqlStatement = @"[dbo].[GetContactsForUser]";
                using (SqlCommand command = new SqlCommand(sqlStatement, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@USERID", userId);
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ContactViewModel contact = new ContactViewModel
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Address = reader["Address"] as string,
                                Email = reader["Email"] as string,
                                Name = reader["Name"] as string,
                                Phone = reader["Phone"] as string,
                                Picture = reader["Picture"] as string,
                                UserId = (int) reader["UserId"],
                                PicWidth = (int)reader["PictureWidth"],
                                PicHeight = (int)reader["PictureHeight"]
                            };
                            contacts.Add(contact);
                        }
                    }
                }
            }

            return contacts;
        }

        public static List<ContactViewModel> GetContactsFiltered(int userId, string filter)
        {
            List<ContactViewModel> contacts = new List<ContactViewModel>();

            using (SqlConnection connection = new SqlConnection(Utilities.Configuration.ConnectionString))
            {
                const string sqlStatement = @"[dbo].[GetContactsForUserFiltered]";
                using (SqlCommand command = new SqlCommand(sqlStatement, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@USERID", userId);
                    command.Parameters.AddWithValue("@FILTER", string.Format("%{0}%", filter));
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ContactViewModel contact = new ContactViewModel
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Address = reader["Address"] as string,
                                Email = reader["Email"] as string,
                                Name = reader["Name"] as string,
                                Phone = reader["Phone"] as string,
                                Picture = reader["Picture"] as string,
                                UserId = (int)reader["UserId"],
                                PicWidth = (int)reader["PictureWidth"],
                                PicHeight = (int)reader["PictureHeight"]
                            };
                            contacts.Add(contact);
                        }
                    }
                }
            }

            return contacts;
        }

        public static ContactViewModel GetContact(int contactId)
        {
            using (SqlConnection connection = new SqlConnection(Utilities.Configuration.ConnectionString))
            {
                const string sqlStatement = @"[dbo].[GetContactById]";
                using (SqlCommand command = new SqlCommand(sqlStatement, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@ID", contactId);
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            ContactViewModel contact = new ContactViewModel
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Address = reader["Address"] as string,
                                Email = reader["Email"] as string,
                                Name = reader["Name"] as string,
                                Phone = reader["Phone"] as string,
                                Picture = reader["Picture"] as string,
                                UserId = (int)reader["UserId"],
                                PicWidth = (int)reader["PictureWidth"],
                                PicHeight = (int)reader["PictureHeight"]
                            };
                            return contact;
                        }
                    }
                }
            }

            return null;
        }

        public static void SaveContact(ContactViewModel contact)
        {
            using (SqlConnection connection = new SqlConnection(Utilities.Configuration.ConnectionString))
            {
                const string sqlStatement = @"[dbo].[SaveContact]";
                using (SqlCommand command = new SqlCommand(sqlStatement, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@ID", contact.Id);
                    command.Parameters.AddWithValue("@NAME", contact.Name);
                    command.Parameters.AddWithValue("@ADDRESS", contact.Address ?? "");
                    command.Parameters.AddWithValue("@EMAIL", contact.Email ?? "");
                    command.Parameters.AddWithValue("@PHONE", contact.Phone ?? "");
                    command.Parameters.AddWithValue("@USERID", contact.UserId);
                    connection.Open();

                    contact.Id = Convert.ToInt32(command.ExecuteScalar());
                }
            }
        }

        public static void SetContactPicture(int contactId, string pictureUrl, int width, int height)
        {
            using (SqlConnection connection = new SqlConnection(Utilities.Configuration.ConnectionString))
            {
                const string sqlStatement = @"[dbo].[UpdatePicture]";
                using (SqlCommand command = new SqlCommand(sqlStatement, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@ID", contactId);
                    command.Parameters.AddWithValue("@PICTURE", pictureUrl);
                    if (width > 0 && height > 0)
                    {
                        command.Parameters.AddWithValue("@WIDTH", width);
                        command.Parameters.AddWithValue("@HEIGHT", height);
                    }
                    connection.Open();

                    command.ExecuteNonQuery();
                }
            }
        }

        public static bool DeleteContact(int id)
        {
            using (SqlConnection connection = new SqlConnection(Utilities.Configuration.ConnectionString))
            {
                const string sqlStatement = @"[dbo].[DeleteContact]";
                using (SqlCommand command = new SqlCommand(sqlStatement, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@ID", id);
                    connection.Open();

                    command.ExecuteNonQuery();
                }
            }
            return true;
        }
    }
}